package persistencia;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import util.Conexion;
import bean.Anaquel;

public class AnaquelDAO {
	
	public List<Anaquel> buscarAnaquel(Anaquel anaquel) throws SQLException {
		String query="select ifnull(trim(ANALMF),'') ANALMF, ifnull(trim(ANCODI),'') ANCODI, ifnull(ANDESC,'') ANDESC, ifnull(ANPRIO,'') ANPRIO, ifnull(ANSITU,'') ANSITU "
				+ "from prodtecni.tanaq where ANSITU in ('01','02') ";
		if(!anaquel.getAnalmv().isEmpty()){
			query+="and upper(ANALMV) like upper(?) || '%' ";
		}
		if(!anaquel.getAnalmf().isEmpty()){
			query+="and upper(ANALMF) like upper(?) || '%' ";
		}
		if(!anaquel.getAncodi().isEmpty()){
			query+="and upper(ANCODI) like upper(?) || '%' ";
		}
		if(!anaquel.getAndesc().isEmpty()){
			query+="and upper(ANDESC) like upper(?) || '%' ";
		}
		if(anaquel.getAnprio()>0){
			query+="and ANPRIO = ?  ";
		}

		PreparedStatement pstm = Conexion.obtenerConexion().prepareStatement(query);
		int parametro = 0;
		if(!anaquel.getAnalmv().isEmpty()){
			pstm.setString(++parametro, anaquel.getAnalmv());
		}
		if(!anaquel.getAnalmf().isEmpty()){
			pstm.setString(++parametro, anaquel.getAnalmf());
		}
		if(!anaquel.getAncodi().isEmpty()){
			pstm.setString(++parametro, anaquel.getAncodi());
		}
		if(!anaquel.getAndesc().isEmpty()){
			pstm.setString(++parametro, anaquel.getAndesc());
		}
		if(anaquel.getAnprio()>0){
			pstm.setInt(++parametro, anaquel.getAnprio());
		}
		ResultSet rs = pstm.executeQuery();
		List<Anaquel> listado = new ArrayList<Anaquel>();
		while(rs.next()){
			Anaquel obj = new Anaquel();
			obj.setAnalmf(rs.getString("ANALMF"));
			obj.setAncodi(rs.getString("ANCODI"));
			obj.setAndesc(rs.getString("ANDESC"));
			obj.setAnprio(rs.getInt("ANPRIO"));
			obj.setAnsitu(rs.getString("ANSITU"));
			listado.add(obj);
		}
		
		rs.close();
		pstm.close();
		return listado;
	}
	
	public List<Anaquel> getAnaqueles(Anaquel anaquel) throws SQLException {
		List<Anaquel> anaqueles = new ArrayList<Anaquel>();
		PreparedStatement pstm = Conexion.obtenerConexion().prepareStatement("select ANALMV,ANALMF,ANCODI,ANDESC,ANPRIO,ANSITU,ALUCRE,ALFCRE,ALHCRE from prodtecni.tanaq where ANALMV=? and ANALMF=? order by ANCODI");
		pstm.setString(1, anaquel.getAnalmv());
		pstm.setString(2, anaquel.getAnalmf());
		ResultSet rs = pstm.executeQuery();
		while(rs.next()){
			Anaquel obj = new Anaquel();
			obj.setAnalmv(rs.getString("ANALMV"));
			obj.setAnalmf(rs.getString("ANALMF"));
			obj.setAncodi(rs.getString("ANCODI"));
			obj.setAndesc(rs.getString("ANDESC"));
			obj.setAnprio(rs.getInt("ANPRIO"));
			obj.setAnsitu(rs.getString("ANSITU"));
			obj.setAlucre(rs.getString("ALUCRE"));
			obj.setAlfcre(rs.getInt("ALFCRE"));
			obj.setAlhcre(rs.getInt("ALHCRE"));
			anaqueles.add(obj);			
		}
		rs.close();
		pstm.close();
		return anaqueles;
	}
	
	public int registrarAnaquel(Anaquel anaquel)throws SQLException{
		PreparedStatement pstm = Conexion.obtenerConexion().prepareStatement(""
				+ "insert into prodtecni.tanaq(ANALMF,ANCODI,ANDESC,ANPRIO,ANSITU,ALUCRE,ALFCRE,ALHCRE,ANALMV) "
				+ "values(?,?,?,?,?,?,?,?,?)");
		pstm.setString(1, anaquel.getAnalmf());
		pstm.setString(2, anaquel.getAncodi());
		pstm.setString(3, anaquel.getAndesc());
		pstm.setInt(4,anaquel.getAnprio());
		pstm.setString(5,anaquel.getAnsitu());
		pstm.setString(6, anaquel.getAlucre());
		pstm.setInt(7,anaquel.getAlfcre());
		pstm.setInt(8,anaquel.getAlhcre());
		pstm.setString(9,anaquel.getAnalmv());
		int result = pstm.executeUpdate();
		pstm.close();
		return result;
	}
	
	public int eliminarAnaquel(Anaquel anaquel)throws SQLException{
		PreparedStatement pstm = Conexion.obtenerConexion().prepareStatement("delete from prodtecni.tanaq where ANALMV=? and ANALMF=?");
		pstm.setString(1, anaquel.getAnalmv());
		pstm.setString(2, anaquel.getAnalmf());
		int result = pstm.executeUpdate();
		pstm.close();
		return result;
	}
	
	public boolean exist(Anaquel anaquel)throws SQLException{
		PreparedStatement pstm = Conexion.obtenerConexion().prepareStatement("select ANCODI from prodtecni.tanaq where ANALMV=? and ANALMF=? and ANCODI=?");
		pstm.setString(1, anaquel.getAnalmv());
		pstm.setString(2, anaquel.getAnalmf());
		pstm.setString(3, anaquel.getAncodi());
		boolean exist = false;
		ResultSet rs = pstm.executeQuery();
		if(rs.next()){
			exist = true;
		}
		rs.close();
		pstm.close();
		return exist;
	}

}
